/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAOOracle;

import DAO.DAOEmpresa;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.LinkedList;
import modelo.Domicilio;
import modelo.Empresa;

/**
 *
 * @author Nai
 */
class EmpresaDAOOracle implements DAOEmpresa {

    public EmpresaDAOOracle() {
    }
    
    public boolean insert(Empresa dire) {
        Connection con = OracleDAOFactory.createConnection();
        boolean res = false;
        try {
            PreparedStatement st = con.prepareStatement("insert into EMPRESAS " +
                    "(RAZON_SOCIAL,DOMICILIO_ID,TELEFONO,CONTACTO,EMAIL) " +
                    "values (?,?,?,?,?)");
            st.setString(1, dire.getRazonSocial());
            st.setInt(2, dire.getDomicilio().getIdDomicilio());
            st.setInt(3, dire.getTelefono());
            st.setString(4, dire.getContacto());
            st.setString(5, dire.getEmail());
            res = st.execute();
            con.close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return res;
    }

    public boolean delete(Empresa dire) {
        Connection con = OracleDAOFactory.createConnection();
        boolean res = false;
        try {
            PreparedStatement st = con.prepareStatement("delete from EMPRESAS where ID_EMPRESA=?");
            st.setInt(1, dire.getIdEmpresa());
            st.execute();
            con.close();
            res = true;

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            res = false;
        }
        return res;
    }

    public boolean update(Empresa dire) {
        Connection con = OracleDAOFactory.createConnection();
        boolean res = false;
        try {
            PreparedStatement st = con.prepareStatement("update Empresas set " +
                    "ID_EMPRESA=?,RAZON_SOCIAL=?,DOMICILIO_ID=?,TELEFONO=?,CONTACTO=?,EMAIL=?" +
                    "where id_empresa=?");

            st.setInt(1, dire.getIdEmpresa());      
            st.setString(2, dire.getRazonSocial());
            st.setInt(3, dire.getDomicilio().getIdDomicilio());
            st.setInt(4, dire.getTelefono());
            st.setString(5, dire.getContacto());
            st.setString(6, dire.getEmail());
            
            res = st.execute();
            con.close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return res;
    }

    public Empresa find(Empresa dire) {
        Connection con = OracleDAOFactory.createConnection();
        Empresa dir = null;

        try {//ID RAZON_SOCIAL,DOMICILIO_ID,TELEFONO,CONTACTO,EMAIL
            ResultSet rs = con.createStatement().executeQuery("select * from EMPRESAS where ID_EMPRESA="+ dire.getIdEmpresa());
            if (rs.next()) {
                dir = new Empresa();
                dir.setIdEmpresa(rs.getInt(1));
                dir.setRazonSocial(rs.getString(2));
                dir.setDomicilio(new Domicilio(rs.getInt(3)));
                dir.setTelefono(rs.getInt(4));
                dir.setContacto(rs.getString(5));
                dir.setEmail(rs.getString(6));
                
            }
            con.close();
        } catch (SQLException ex) {
        }
        return dir;
    }

    public Collection<Empresa> list() {
     
        Connection con = OracleDAOFactory.createConnection();
        LinkedList<Empresa> res = new LinkedList();
        try {
            PreparedStatement st = con.prepareStatement("select * from empresas order by razon_social");
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                Empresa aux = new Empresa();
                aux.setIdEmpresa(rs.getInt(1));
                aux.setRazonSocial(rs.getString(2));
                aux.setDomicilio(new Domicilio(rs.getInt(3)));
                aux.setTelefono(rs.getInt(4));
                aux.setContacto(rs.getString(5));
                aux.setEmail(rs.getString(6));
                res.add(aux);
            }
            con.close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return res;
        
    }
    
       public Collection<Empresa> buscarPorRazon(String ape) {
        Connection con = OracleDAOFactory.createConnection();
        LinkedList<Empresa> res = new LinkedList();
        try {
            ResultSet rs = con.createStatement().executeQuery("SELECT  E.RAZON_SOCIAL, e.telefono, e.contacto, e.email "
                    + "FROM EMPRESAS E WHERE UPPER(e.razon_social) LIKE UPPER('" + ape + "%')");
            while (rs.next()) {
                Empresa p = new Empresa();
                p.setRazonSocial(rs.getString(1));
                p.setTelefono(rs.getInt(2));
                p.setContacto(rs.getString(3));
                p.setEmail(rs.getString(4));
           
                res.add(p);
            }
            con.close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return res;
    }
    
}
